//    The page gives admin the functionality to Upload the Medical School's Data from a file(XLS)         
//    The data will be inserted/updated to the tbl_medicalschool table.                                
//    Author: Rupinder Saini, Ajay Kumar Singh                                                              
//    Date: August 30,2006    




using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text.RegularExpressions;
using SiteManager;
using System.Data.SqlClient;

public partial class Admin_Ad_UploadMedicalSchools : System.Web.UI.Page
{// creating an instance of manager class
    Manager mgr = new Manager(true,"ConnectionString");

    public DataTable dt_Err;  // dtDoctors_Err
    protected void Page_Load(object sender, EventArgs e)
    {
        lblMsg.Text = "";
    }

   
    /// Uploading the file to Temporary folder in the server and reading its data and if the CSV contains
    /// correct data then save the data in the DataBase else show the error.

    #region uploadbutton

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (uplFileUpload.HasFile)
        {
            string contentType = uplFileUpload.PostedFile.ContentType;
            //Response.Write("Content Type:"+contentType);
            string filename = uplFileUpload.PostedFile.FileName;

            // Checking the data for the uploaded file , should be Excel only
            if ((contentType.Equals("application/octet-stream") || contentType.Equals("application/excel") || contentType.Equals("application/vnd.ms-excel") || contentType.Equals("application/x-excel") || contentType.Equals("application/x-msexcel")) && filename.Substring(filename.LastIndexOf('.') + 1).ToUpper().Equals("XLS"))
            {
                // checking whether the file has no data 
                if (uplFileUpload.PostedFile.ContentLength > 0)
                {
                   //try
                    {
                        // saving the file to the Temporary folder
                        uplFileUpload.SaveAs(Server.MapPath(@".\TemporaryData\") + uplFileUpload.FileName);
                        ExcelReader exReader = new ExcelReader(Server.MapPath(@".\TemporaryData\") + uplFileUpload.FileName);
                        string sheetName = exReader.GetExcelSheetNames()[0];

                        //checking for the correct data and if data is error free inserting it into the Database
                     //  try
                        {
                            CheckDataToMedScoolTable(exReader.GetDataTable(sheetName));

                        }
                        //catch (Exception es)
                        //{
                        //    lblMsg.Text = es.Message;
                        //}

                    }
                    //catch (Exception ex)
                    
                    //{
                    //  lblMsg.Text = ex.Message;
                    //    lblMsg.ForeColor = System.Drawing.Color.Red;
                    //    return;
                    //}
                    //finally
                    {
                        //Delete File
                        System.IO.FileInfo fi = new System.IO.FileInfo(Server.MapPath(@".\TemporaryData\") + uplFileUpload.FileName);
                        fi.Delete();
                    }
                }
                else
                {
                    lblMsg.Text = "File contains no data!!";
                    lblMsg.ForeColor = System.Drawing.Color.Red;
                    return;
                }
            }
            else
            {
                lblMsg.Text = "InCorrect File Type. Please select only XLS file to upload!!";
                lblMsg.ForeColor = System.Drawing.Color.Red;
                return;
            }
        }
        else
        {
            lblMsg.Text = "No uploaded file!!";
            lblMsg.ForeColor = System.Drawing.Color.Red;
            return;
        }
    }

  #endregion


    //This functiom reads in data from the DataTable for Inserting into the DataBase 
    #region CheckDataToMedScoolTable
    
    private void CheckDataToMedScoolTable(DataTable dt)
    {
        // Table to store error Records in the tables columns if any        
        dt_Err = new DataTable();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            dt_Err.Columns.Add(dt.Columns[i].ColumnName,Type.GetType("System.String") );            
        }

        bool IsError = false;
       // Response.Write(dt.Columns.Count);
        if (dt.Columns.Count == 2)
        {
            //reading data for each row of the DataTable (from xls) 
            if (dt.Rows.Count > 0)
            {
                #region loop
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    bool IsInsertRow = false;
                    DataRow MschRow = dt_Err.NewRow();
                    if (dt.Rows[i][1].ToString().Trim().Length > 0)
                    {
                        try
                        {
                           // Response.Write(dt.Rows[i][1].ToString().Trim().Replace("'", "''").TrimEnd('%'));
                            dt.Rows[i][1] = Convert.ToDouble(dt.Rows[i][1].ToString().Trim().Replace("'", "''").TrimEnd('%')) * 100;
                        }
                        catch { }
                    }
                   
                    MschRow[0] = dt.Rows[i][0];
                    MschRow[1] = dt.Rows[i][1];
                    
                    string tSName = MschRow["Medical School"].ToString().Trim().Replace("'", "''");
                    string tUSRating = MschRow["US Rating"].ToString().Trim().Replace("'", "''").TrimEnd('%');

                    //checking for errors in the varibales and setting the suitable error message in the corrresponding Column

                    if (checkLength_DataType(tSName, "string", 500, 1).Length > 0)
                    {
                        IsInsertRow = true;
                        MschRow.SetColumnError("Medical School", checkLength_DataType(tSName, "string", 500, 1));
                        MschRow["Medical School"] = "<font color='red'>" + MschRow["Medical School"] + "</font>";
                    }

                    //for to update already exist medical school
                    //else  // checking for duplicates
                    //{
                    //    if (IsExistMedSch(tSName))
                    //    {
                    //        IsInsertRow = true;
                    //        MschRow.SetColumnError("Medical School", "The Medical School Name already exist in the database!!Please try with a different name.");
                    //        MschRow["Medical School"] = "<font color='red'>" + MschRow["Medical School"] + "</font>";
                    //    }
                    //}


                    if (checkLength_DataType(tUSRating, "int", 0,1).Length > 0)
                    {
                        IsInsertRow = true;
                        MschRow.SetColumnError("US Rating", checkLength_DataType(tUSRating, "int", 0, 1));
                        MschRow["US Rating"] = "<font color='red'>" + MschRow["US Rating"] + "</font>";
                    }
                    if (IsInsertRow)
                    {
                        //dt_Err.ImportRow(MschRow);
                        dt_Err.Rows.Add(MschRow);
                        IsError = true;
                    } 
               
               }
 #endregion

              #region Final Action
                     #region Error
                       if (IsError)
                       {
                           //Show the rows containing errors in the DataGrid  
                           grdErrors.Visible = true;
                           grdErrors.DataSource = dt_Err;
                           grdErrors.DataBind();
                           lblMsg.Text = "Some rows in the XLS file contains errors!!<br>Please see the error rows (Columns in red color) in the listing below.<br>To view error details place your mouse over the red area<br>Please correct the errors and upload the file again.";
                           lblMsg.ForeColor = System.Drawing.Color.Red;
                           return;
                       } 
                    #endregion

                else
                {
                     //Insert the records into database
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow MschRow = dt.Rows[i];
                        string tSName = MschRow["Medical School"].ToString().Trim();
                        string tUSRating = MschRow["US Rating"].ToString().Trim().Replace("'", "''").TrimEnd('%');
                       
                        //Get the corresponding ADOCRating id for US rating
                        if (Convert.ToDouble(tUSRating) > 100)
                        {
                            tUSRating = "100.00";
                        }
                      
                        decimal dUSRating = GetADOCRating(tUSRating);
                        //string sqlQuery = "";
                        //sqlQuery = "SET NOCOUNT ON ";
                        //sqlQuery += "insert into tbl_medicalschool(MedicalSchool,Rating,RegDate)values('";
                        //sqlQuery += tSName + "'," + dUSRating + ",'" + getdate() + "')";
                        //sqlQuery += tSName + "'," + dUSRating + ",getdate())";
                        //Response.Write("Query = "+sqlQuery);
                        //Response.End();

                        // new changes to update existing record and insert new record
                        SqlParameter[] sqlParam = new SqlParameter[2];
                        sqlParam[0] = new SqlParameter("@MedicalSchool", SqlDbType.VarChar);
                        sqlParam[1] = new SqlParameter("@Rating", SqlDbType.Decimal);
                        sqlParam[1].Precision = 18;
                        sqlParam[1].Scale = 6;
                        
                        sqlParam[0].Value = tSName.Trim();
                        sqlParam[1].Value = dUSRating;

                        mgr.ExecuteUpdate("dbo.Upload_MedicalSchool", sqlParam);

                    //try
                        //{
                            
                        //   mgr.ExecuteUpdate(sqlQuery);
                        //}
                       //catch (Exception ex)
                       // {
                       //     lblMsg.Text = ex.ToString();
                       //     lblMsg.ForeColor = System.Drawing.Color.Red;
                       //     return;
                       // }
                    }
                     lblMsg.Text = "File " + uplFileUpload.FileName.ToString() + " Uploaded successfully..";
                    lblMsg.ForeColor = System.Drawing.Color.Green;
                    grdErrors.Visible = false;
                }

    }
                # endregion
            
            else
            {
                //No rows found in the table
                lblMsg.Text = "No Data!!";
                lblMsg.ForeColor = System.Drawing.Color.Red;
                return;
            }
        }
        else
        {
            //Wrong no. of Columns
            lblMsg.Text = "Wrong no. of Columns!!";
            lblMsg.ForeColor = System.Drawing.Color.Red;
            return;
        }
    }

     #endregion

    

#region generalfunctions

    /// <summary>
    /// check datatype, etc. of a column
    /// </summary>
    /// <param name="var"></param>
    /// <param name="dataType"></param>
    /// <param name="maxLen"></param>
    /// <param name="required"></param>
    /// <returns></returns>
    private string checkLength_DataType(string var, string dataType, int maxLen, int required)
    {
        if (dataType == "string")
        {
            if (required == 1)
            {
                if (var.Trim().Length <= 0)
                    return "The value of this column can not be empty!!";
            }

            if (var.Length > maxLen)
                return "Maximium length of this field should be <=" + maxLen.ToString();
        }
        else if (dataType == "int")
        {
            if (required == 1)
            {
                if (var.Trim().Length <= 0)
                    return "The value of this column can not be empty!!";
            }
            //else
            {
               try
                {
                    decimal test = Convert.ToDecimal(var);
                }
                catch
                {
                    return " DataType MisMatch. Decimal Type is required!!";
                }
            }
        }
        return "";
    }

    /// <summary>
    /// checking for dupliacte name of the medical school
    /// </summary>
    /// <param name="inputname"></param>
    /// <returns></returns>
    public bool IsExistMedSch(String inputname)
    {
        string sql = "select count(*) from tbl_medicalschool where MedicalSchool='" + inputname.Trim() + "' and isdelete=0";
        DataSet ds = mgr.ExecuteQuery(sql, "tbl");
        int count = (int)ds.Tables[0].Rows[0][0];
        if (count > 0)
            return true;
        else
            return false;
    }

    /// <summary>
    /// To convert US rating to ADOC rating
    /// </summary>
    /// <param name="USrating"></param>
    /// <returns></returns>
    private decimal GetADOCRating(string USrating)
    {
        if (USrating.Trim().Length <= 0)
        {
            return 0;
        }
        else
        {
            return ((Convert.ToDecimal(5.00 - 3.33) * Convert.ToDecimal(USrating) / Convert.ToDecimal(100) + Convert.ToDecimal(3.33)));
        }
         
    }
#endregion
  
    
    protected void grdErrors_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            for (int i = 0; i < e.Row.Cells.Count; i++)
            {
                e.Row.Cells[i].Text = Server.HtmlDecode(e.Row.Cells[i].Text);

                //try
                {
                    //displaying the error messages tooltip for the rows,columns
                    e.Row.Cells[i].ToolTip = dt_Err.Rows[e.Row.RowIndex].GetColumnError(i);
                    if (dt_Err.Rows[e.Row.RowIndex].GetColumnError(i).Equals("The value of this column can not be empty!!"))
                    {
                        e.Row.Cells[i].BackColor = System.Drawing.Color.Red;
                        e.Row.Cells[i].ForeColor = System.Drawing.Color.Red;
                    }

                }
               // catch { }
            }
            e.Row.Cells[1].Text = e.Row.Cells[1].Text + "%";
        }
    }
}
